package com.lbssoft.gpsbd.dao;

import com.lbssoft.gpsbd.model.Log;
import com.lbssoft.gpsbd.model.LoginLog;
import com.lbssoft.gpsbd.model.vo.Counts;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface StatisticsMapper {

	/**
	 *  统计当日设备统计信息
     */
	@Insert("INSERT INTO lbs_statistics_device ( deviceId, geofenceEnter, geofenceExit, sdate ) SELECT t.id AS deviceId, IFNULL(c.geofenceEnter, 0) AS geofenceEnter, IFNULL(c.geofenceExit, 0) AS geofenceExit, date(now()) AS sdate FROM lbs_devices t LEFT JOIN ( SELECT deviceId AS alarmDeviceId, sum( CASE type WHEN 2 THEN totalAlarm END ) AS geofenceEnter, sum( CASE type WHEN 3 THEN totalAlarm END ) AS geofenceExit FROM ( SELECT deviceId, type, COUNT(*) AS totalAlarm FROM `lbs_events` WHERE date_format(time, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d') GROUP BY deviceId, type ) temp GROUP BY deviceId ) c ON c.alarmDeviceId = t.id")
	void deviceTodayStatisics();

	/**
	 * 重置当日里程
	 */
//	@Update("UPDATE lbs_devices SET todaytrip = 0")
//	void resetTodayTrip();

	/**
	 * 根据用户id获取设备统计
	 * @param userId
	 * @return
     */
	@Select("SELECT b.id,b.name,GROUP_CONCAT(t.${column} ORDER BY t.sdate) as vals,GROUP_CONCAT(t.sdate ORDER BY t.sdate) as sdates FROM lbs_statistics_device t LEFT JOIN lbs_devices b on b.id = t.deviceid WHERE t.deviceid in (SELECT deviceid FROM lbs_user_device a where a.userid = #{userId} and a.deviceid in(${deviceIds})) and t.sdate>=#{start} and t.sdate<=#{end} GROUP BY b.name")
	List<Map> getDeviceStatisicsByUserId(@Param("userId") long userId,@Param("deviceIds") String deviceIds,@Param("column") String column,@Param("start") String start,@Param("end") String end);

	/**
	 * 统计用户 设备 报警 围栏总数
	 * @return
     */
	@Select("SELECT * FROM ( SELECT sum(online) as userOnline,sum(offline) as userCount FROM (SELECT (CASE WHEN `status`=1 THEN sum(1) ELSE 0 END) as online,count(*) as offline FROM lbs_users GROUP BY `status`) temp ) AS a, ( SELECT COUNT(*) AS deviceCount FROM lbs_devices ) AS b, ( SELECT COUNT(*) AS eventCount FROM lbs_events ) AS c, ( SELECT COUNT(*) AS geofenceCount FROM lbs_geofences ) AS d, ( SELECT max(online) AS online, max(offline) AS offline, max(notactive) AS notactive FROM ( SELECT ( CASE STATUS WHEN 1 THEN SUM(1) ELSE 0 END ) ONLINE, ( CASE STATUS WHEN 0 THEN SUM(1) ELSE 0 END ) offline, ( CASE WHEN STATUS IS NULL THEN SUM(1) ELSE 0 END ) notactive FROM lbs_devices GROUP BY `status` ) temp ) AS e,(SELECT COUNT(*) as unread FROM lbs_events WHERE isread = false) as f")
	Counts getCounts();

	/**
	 * 更新统计报表里的当日设备里程
	 * @param deviceId
	 * @param trip
     */
	@Update("UPDATE lbs_statistics_device SET trip = #{trip} WHERE deviceid = #{deviceId} AND to_days(sdate) =  to_days(now())")
	void updateTodayStatisicsTrip(@Param("deviceId") long deviceId,@Param("trip") double trip);


}
